package br.com.fiap.trabalho.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import br.com.fiap.trabalho.entity.Category;
import br.com.fiap.trabalho.entity.Movie;

public class JDBCCategoryDAO implements CategoryDAO {
	
	private Map<Integer, Category> categoriesMap = new HashMap<Integer, Category>();
	
	public Category createCategory(Category category) {
		
		Connection connection = null;
		
		try {
			connection = JDBCConnectionManager.getConnection();
			
			connection.setAutoCommit(false);
			
			if (category.getId() == null) {
				insertCategory(category, connection);
				
				if (category.getMovies() != null && !category.getMovies().isEmpty()) {
					for (Movie movie : category.getMovies()) {
						List<Category> categories = movie.getCategories();
						
						if (categories == null || categories.isEmpty()) {
							categories = new LinkedList<Category>();
							movie.setCategories(categories);
						}
						
						categories.add(category);
						new JDBCMovieDAO().createMovie(movie);
					}
				}
				
			} else {
				updateCategory(category, connection);
			}
			
			connection.commit();
			connection.setAutoCommit(true);
			
		} catch (Exception e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			category = null;
		} finally {
			if (connection != null) {
				JDBCConnectionManager.closeConnection(connection);
			}
		}
		
		return category;
		
	}
	
	public boolean deleteCategory(Category category) {
		
		Connection connection = null;
		
		boolean deleted = false;
		
		try {
			connection = JDBCConnectionManager.getConnection();
			
			String sql = "delete from category where id = ?";
			
			PreparedStatement ps = connection.prepareStatement(sql);
			
			ps.setInt(1, category.getId());
			ps.execute();
			
			deleted = true;
			
			ps.close();
			
		} catch (Exception e) {
			e.printStackTrace();
			
		} finally {
			if (connection != null) {
				JDBCConnectionManager.closeConnection(connection);
			}
		}
		
		return deleted;
		
	}

	public List<Category> selectCategoryByName(String name) {
		
		List<Category> categories = new ArrayList<Category>();
		Connection connection = null;
		
		try {
			
			connection = JDBCConnectionManager.getConnection();
			
			StringBuilder sql = new StringBuilder();
			
			sql.append(" select c.id as category_id, c.name as category_name, m.id as movie_id, m.title as movie_title, m.year_movie as movie_year ");
			sql.append(" from category c left join movie_actor ma on ma.actors_id = c.id ");
			sql.append(" left join movie m on ma.movies_id = m.id ");
			sql.append(" where c.name like ? ");
			
			PreparedStatement ps = connection.prepareStatement(sql.toString());
			ps.setString(1, "%"+name+"%");

			ResultSet rs = ps.executeQuery();
			
			while(rs.next()) {
				categories.add(getResutSetCategory(rs));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			return null;
			
		} finally {
			if (connection != null) {
				JDBCConnectionManager.closeConnection(connection);
			}
		}
		
		return categories;
		
	}
	
	private void setIdOnEntity(Category category, PreparedStatement ps) throws SQLException {
		
		ResultSet generatedKeys = ps.getGeneratedKeys();
		if (generatedKeys.next()) {
		    category.setId(generatedKeys.getInt(1));
		}
		generatedKeys.close();
		
	}
	
	private void insertCategory(Category category, Connection connection)
			throws SQLException {
		String sql = "insert into category (name) values (?)";
		
		PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
		ps.setString(1, category.getName());
		
		ps.executeUpdate();
		
		setIdOnEntity(category, ps);
		
		ps.close();
	}
	
	private void updateCategory(Category category, Connection connection)
			throws Exception {
		
		String selectById = "select * from category where id = ?";
		
		PreparedStatement ps = connection.prepareStatement(selectById);
		ps.setInt(1, category.getId());
		
		ResultSet rs = ps.executeQuery();
		
		Category persistedCategory = null;
		
		if (rs.next()) {
			persistedCategory = new Category();
			
			persistedCategory.setId(rs.getInt("id"));
			persistedCategory.setName(rs.getString("name"));
		}
		
		if (persistedCategory == null) {
			throw new Exception("No Category found with id = " + category.getId());
		}
			
		String sql = "update category set name = ?";
		
		ps = connection.prepareStatement(sql);
		ps.setString(1, category.getName());
		
		ps.executeUpdate();
		
		ps.close();
	}
	
	private Category getResutSetCategory(ResultSet rs) throws SQLException {
		
		Category cachedCategory = categoriesMap.get(rs.getInt("category_id"));
		
		if (cachedCategory == null) {
			cachedCategory = new Category();
			
			cachedCategory.setId(rs.getInt("category_id"));
			cachedCategory.setName(rs.getString("category_name"));
			
			try{
				rs.getInt("movie_id");
			}catch(SQLException sqe){
				return cachedCategory;
			}
			
			categoriesMap.put(cachedCategory.getId(), cachedCategory);
		}
		
		List<Movie> movies = cachedCategory.getMovies();
		
		try{
			rs.getInt("movie_id");
		}catch(SQLException sqe){
			return cachedCategory;
		}
		
		Integer idMovie = rs.getInt("movie_id");
		
		if (idMovie != 0 && movies == null) {
			movies = new ArrayList<Movie>();
			cachedCategory.setMovies(movies);
			
			Movie movie = new Movie();
			movie.setId(rs.getInt("movie_id"));
			movie.setTitle(rs.getString("movie_title"));
			movie.setYear(rs.getInt("movie_year"));
			
			movies.add(movie);
		}
		
		return cachedCategory;
	}

	public List<Category> selectCategories(int numberOfElements) {
		
		List<Category> categories = new ArrayList<Category>();
		Connection connection = null;
		
		try {
			
			connection = JDBCConnectionManager.getConnection();
			
			StringBuilder sql = new StringBuilder();
			
			sql.append(" select c.id as category_id, c.name as category_name");
			sql.append(" from category c");
			
			PreparedStatement ps = connection.prepareStatement(sql.toString());
		
			ps.setMaxRows(numberOfElements);

			ResultSet rs = ps.executeQuery();
			
			while(rs.next()) {
				categories.add(getResutSetCategory(rs));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			return null;
			
		} finally {
			if (connection != null) {
				JDBCConnectionManager.closeConnection(connection);
			}
		}
		
		return categories;
	}

}
